#!/usr/bin/env python2
# Copyright 2012 Patrick Curran <patrickthebold@gmail.com>
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.

# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.

# You should have received a copy of the GNU General Public License
# along with this program.  If not, see <http://www.gnu.org/licenses/>.

import pyodbc
import xml.etree.cElementTree as ET
import sys
import itertools
import re
from getpass import getpass
from string import Template
from xml.sax.saxutils import escape

# Set the encoding of your database. On MSSQL server SELECT SERVERPROPERTY('Collation')
# will output the encoding, amongst other things. 
enc='Latin-1'

# pyodbc connection object. It uses a Data Source Name to connect, this might be set in
# /etc/odbc.ini. See your ODBC documentation. unixodbc works well.
cnxn = pyodbc.connect('DSN='+sys.argv[2]+';UID='+raw_input('Username: ')+';PWD='+getpass())

# Parse the XML from the given file.
tree = ET.ElementTree(file=sys.argv[1])

# This is used to control the namespace for all of the evaluated python code in the above XML file.
globalfunc = {}

# d is a dictionary whose keys are tuples. fix_keys will return a new dictionary that is multi
# indexed instead. So for example: instead of d[(a,b,c)] you'd use d[a][b][c]. If the keys of d have 
# several of the first elements in common. i.e. whenever (x,y,z) is a key then x = some constant.
# Those parts are informationally useless they should be included in keys and they will not be part
# of the returned dictionary.
def fix_keys(d, keys):
	ret = {}
	i = ret
	for k, v in d.iteritems():
		for j in range(len(k)):
			if j == len(k)-1:
				i[k[j]]=v
			elif not k[j] in keys:
				if not i.has_key(k[j]):
					i[k[j]]={}
				i=i[k[j]]
		i=ret
	return ret

# Everything with 'oc' in the name is to handle 'Out of Context' replacement. Normally the strings in
# the 'in' attribute get replaced by values that are functionally dependent on the 'key' attribute.
# If one of this 'in' strings live in the parent or other ancestor of the 'replace' element in
# question, then this is now a multi-valued object. In fact it will be a multi-indexed dictionary as
# returned by get_keys. (The number of indices goes up by 1 for every ancestor.)
# 
# make_oc updates all of the tracked values oc it a tuple of dictionaries. d is an element of this
# tuple. kwargs holds the values that we need to insert. k and the keys to kwargs are 'in' values of
# the descendents. These are the strings that will get replaced by the values of kwargs. keys is a
# tuple of functions that extracts the values from the tuple i. i is a tuple returned by the SQL
# query.
def make_oc(d, k, kwargs, keys, i):
	if kwargs.has_key(k):
		d[tuple(map(lambda x, y: x(y), keys, i))]=kwargs[k]

# split splits the string s based on the delimiter d. It allows for some escaping: if d is preceded
# by a backslash split will not split there. Ideally this would take into account if d is preceded
# by an odd or even number of backslashes, but it doesn't.
def split(s, d):
	return map(lambda x: str.replace(x, '\\'+d, d), re.split(r'(?<!\\)'+d, s))

# rep replaces the substring i in the string s with the string o. It handles escapes the same was as
# split.
def rep(s, i, o):
	return re.sub(r'(?<!\\)'+i, o, s).replace('\\'+i, i)

# tofunc takes and python expression that uses '$' as the tuple returned by the database and returns
# the function that yields the expected value when given such a tuple. As you can see we politely
# ask that 'pleasedontusethisasatoken' is not used in the python expression that gets evaluated. As
# and example if s=$[1]+$[3] then the function returned takes (1,2,3,4) -> 2+4 = 6.
def tofunc(s):
	return eval('lambda pleasedontusethisasatoken: '+rep(s, '\\$', 'pleasedontusethisasatoken'), globalfunc)

# This works like tofunc, but instead of using $ to represent the tuple returned by the database
# $instring represents the multi-indexed dictionary that is tracked and hence and 'in' string for a
# descendent.
def tofunc_oc(s, shorttrack):
	sub_dict=dict(zip(shorttrack, map(lambda x: 'pleasdontusethisasatoken[trackoc.index('+x+')]', shorttrack)))
	return eval('lambda pleasedontusethisasatoken: '+ Template(s).safe_substitute(sub_dict), globalfunc)

# This will cleanup (hopefully) the XML if there happens to be any XML like characters in the
# database.
def nice(s):
	if s:
		return escape(str(s))
	else:
		return 'None'

# rows is the entire list of tuples returned by the database. We split this into a list of lists
# where keys is constant on each sublist. This is assuming that rows is already ordered by keys. I
# should add in option to do the sorting locally if for some reason the database can't so the
# sorting for us.
def distinct(rows, keys):
	if len(rows) == 0:
		return []
	ret=[[rows[0]]]
	for i in range(len(rows)-1):
		if map(lambda x: x(rows[i]), keys)!= map(lambda x: x(rows[i+1]), keys):
			ret.append([])
		ret[len(ret)-1].append(rows[i+1])
	return ret

# This is the main recursive function that descends through the XML. Its a Depth first search. The
# return value is a list of elements that should replace e in the tree.  
# cnxn is the connection, probably could be a global, but we're passing it around anyway.
# e is the element we are on. rows is the relevant list of rows returned by the database. keys is a
# tuple of functions that map over rows that control the functional dependencies of replacing 'in'
# attributes with 'out' attributes. oc is the tuple of dictionaries that allow for 'in' values to be
# used in the ancestor replace elements. trackoc is the tuple of 'in' elements that we are tracking
# so the values stored in oc can be replaced when we reach them.
def f(cnxn, e, rows=None, keys=(), oc=(), trackoc=()):
	ret = [] # List of elements to return

	if e.tag == 'SQL':
		# Get data from the database
		cursor = cnxn.cursor()
		try:
			cursor.execute(e.attrib['cmd'], split(e.attrib['params'], '\\?'))
		except KeyError:
			cursor.execute(e.attrib['cmd'])
		# This could be a memory issue, but it is easy. First place to optimize if needed.
		rows=cursor.fetchall()
		# return f applied to the children.
		for i in e:
			ret.extend(f(cnxn, i, rows, keys, oc, trackoc))
	
	elif e.tag == 'replace':
		# parse the substitution args
		try:
			inp=split(e.attrib['in'], '\\?')
			out=map(tofunc, split(e.attrib['out'], '\\?'))
		except KeyError:
			inp=[]
			out=[]
		tmp=list(keys)
		tmp.append(tofunc(e.attrib['key']))
		keys = tuple(tmp)
		# Do the same with aggregate args
		try:
			inpagg=split(e.attrib['inagg'], '\\?')
			outagg=map(tofunc, split(e.attrib['outagg'], '\\?'))
			funcagg=map(lambda x: eval(x, globalfunc), split(e.attrib['funcagg'], '\\?'))
		except KeyError:
			inpagg=[]
			outagg=[]
			funcagg=[]
		# Do the same with oc args
		try:
			inpoc=split(e.attrib['inoc'], '\\?')
			shorttrack=split(e.attrib['trackoc'], '\\?')
			outoc=map(lambda x: tofunc_oc(x, shorttrack), split(e.attrib['outoc'], '\\?'))
			funcoc=map(lambda x: eval(x, globalfunc), split(e.attrib['funcoc'], '\\?'))
		except KeyError:
			inpoc=[]
			outoc=[]
			funcoc=[]
			shorttrack=[]
		# This is a bit round about, but I'd like to keep the default parameters immutable
		tmp=list(trackoc)
		tmp.extend(shorttrack)
		trackoc=tuple(tmp)
		tmp=list(oc)
		# Extend oc by empty dictionaries, one for each new string we are tracking
		tmp.extend(itertools.repeat({} , len(shorttrack)))
		oc=tuple(tmp)
		s = ET.tostring(e, encoding=enc)
		for i in distinct(rows, keys):
			# The 0 is because of the assumed functional dependencies
			kwargs=dict(zip(inp, map(lambda z: nice(z(i[0])), out)))
			# Here we are using map for the aggregate function.
			kwargs.update(zip(inpagg, map(lambda z, y: nice(y(map(z, i), kwargs)), outagg, funcagg)))
			# Up date oc, the tuple of out of context dictionaries
			map(lambda x, k: make_oc(x, k, kwargs, keys, i), oc, trackoc) 
			# tmp is what we are appending to ret for this iteration
			tmp=[]
			for j in ET.fromstring(Template(s).safe_substitute(kwargs)):
				 tmp.extend(f(cnxn, j, i, keys, oc, trackoc))
			# oc2 is a list of dictionaries, similar to oc, but with the keys fixed
			oc2=map(lambda x: fix_keys(oc[trackoc.index(x)], map(lambda x, y: x(y), keys,i)), shorttrack)
			# kwargs2, like, kwargs is simply used for substitution.
			kwargs2=dict(zip(inpoc, map(lambda x, y: y(x(oc2), kwargs), outoc, funcoc)))
			# Second round of processing on tmp
			ret.extend(map(lambda x: ET.fromstring(Template(ET.tostring(x, enc)).safe_substitute(kwargs2)), tmp))

	elif e.tag == 'file':
		with open(e.attrib['name'], 'w') as fi:
			# Probably not the best idea to hard code in a doctype for the html files that 
			# get generated, but for now it is fine.
			fi.write("<!DOCTYPE html>\n")
			# We are assuming that the file element has one child, html element
			ret=f(cnxn, e[0], rows, keys, oc)
			ET.ElementTree(ret[0]).write(fi, encoding=enc, method="html")
			fi.close()

	elif e.tag == 'functions':
		for i in e:
			exec i.text.lstrip() in globalfunc
		return []

	else:
		newchildren = map(lambda x: f(cnxn, x, rows, keys, oc),e)
		map(e.remove, e) 
		e.extend(itertools.chain(*newchildren))
		ret.append(e)

	return ret

#Call the main function
f(cnxn, tree.getroot())
